
import pandas as pd
from openpyxl import load_workbook

# 读取Excel文件
file_name = "D:\装机报表\装机报表20230613.xlsx"
sheet_1 = "报表"
sheet_2 = "装换机"
sheet_3 = "串码"

# 替换为实际的列名
column_name_convert = "工单类型"
column_name_handler = "处理人姓名"
column_name_report = "工单类型（新装、移机、换机）"
column_name_report_quxian = "领取区县"
column_name_report_lyr = "领用人"
column_name_report_azr = "安装人"

column_name_report_SN = "领取SN码"
column_name_convert_SN = "设备SN码"

column_name_report_WG = "所属网格"
column_name_convert_WG = "所属班组"

column_name_report_sysj = "使用时间"
column_name_convert_sysj = "竣工时间"

column_name_report_gdbh = "工单编号"
column_name_convert_gdbh = "工单号"
#领取区县列

# 读取报表和装换机sheets，并将第3行设置为列名
print("读取报表和装换机sheets...")
df_report = pd.read_excel(file_name, sheet_name=sheet_1, header=2)
df_convert = pd.read_excel(file_name, sheet_name=sheet_2, header=0)

# 获取报表和装换机列的列索引
report_column_index = df_report.columns.get_loc(column_name_report) + 1

report_column_lyr_index = df_report.columns.get_loc(column_name_report_lyr) + 1
report_column_azr_index = df_report.columns.get_loc(column_name_report_azr) + 1

column_name_convert_SN_index = df_convert.columns.get_loc(column_name_convert_SN) + 1
column_name_report_SN_index = df_report.columns.get_loc(column_name_report_SN) + 1

column_name_convert_WG_index = df_convert.columns.get_loc(column_name_convert_WG) + 1
column_name_report_WG_index = df_report.columns.get_loc(column_name_report_WG) + 1

column_name_convert_sysj_index = df_convert.columns.get_loc(column_name_convert_sysj) + 1
column_name_report_sysj_index = df_report.columns.get_loc(column_name_report_sysj) + 1

column_name_convert_gdbh_index = df_convert.columns.get_loc(column_name_convert_gdbh) + 1
column_name_report_gdbh_index = df_report.columns.get_loc(column_name_report_gdbh) + 1

handler_column_index = df_convert.columns.get_loc(column_name_handler) + 1
column_name_report_quxian_index = df_report.columns.get_loc(column_name_report_quxian) + 1
convert_column_index = df_convert.columns.get_loc(column_name_convert) + 1

# 加载工作簿
print("加载工作簿...")
workbook = load_workbook(file_name)
ws_report = workbook[sheet_1]
ws_convert = workbook[sheet_2]

# 删除报表工作表第4行及以下的所有数据
print("开始删除原有数据...")
rows_to_delete = ws_report.max_row - 3
ws_report.delete_rows(4, rows_to_delete)
print("原有数据已删除。")

# 初始化报表的行数
report_row_num = 4

# 迭代装换机的C列，从第二行开始
print("开始处理装换机数据...")
total_rows = ws_convert.max_row - 3
for row_num in range(4, ws_convert.max_row + 1):
    value = ws_convert.cell(row=row_num, column=convert_column_index).value
    if value == "装" or value == "换":
        ws_report.cell(row=report_row_num, column=1, value=report_row_num-3)
        handler_name = ws_convert.cell(row=row_num, column=handler_column_index).value
        ws_report.cell(row=report_row_num, column=report_column_index, value=value)
        ws_report.cell(row=report_row_num, column=report_column_lyr_index, value=handler_name)
        ws_report.cell(row=report_row_num, column=report_column_azr_index, value=handler_name)
        ws_report.cell(row=report_row_num, column=column_name_report_quxian_index, value="观山湖区")

        column_name_convert_SN_value = ws_convert.cell(row=row_num, column=column_name_convert_SN_index).value
        ws_report.cell(row=report_row_num, column=column_name_report_SN_index, value=column_name_convert_SN_value)

        column_name_convert_WG_value = ws_convert.cell(row=row_num, column=column_name_convert_WG_index).value
        ws_report.cell(row=report_row_num, column=column_name_report_WG_index, value=column_name_convert_WG_value)

        column_name_convert_sysj_value = ws_convert.cell(row=row_num, column=column_name_convert_sysj_index).value
        ws_report.cell(row=report_row_num, column=column_name_report_sysj_index, value=column_name_convert_sysj_value)

        column_name_convert_gdbh_value = ws_convert.cell(row=row_num, column=column_name_convert_gdbh_index).value
        ws_report.cell(row=report_row_num, column=column_name_report_gdbh_index, value=column_name_convert_gdbh_value)

        # 更新报表的行数
        report_row_num += 1
        # 显示处理进度
        progress = (row_num - 3) / total_rows * 100
        print(f"处理进度：{progress:.2f}%")

print("装换机数据处理完成。")

# 保存工作簿
print("保存工作簿中...")
workbook.save(file_name)
print("工作簿已保存。")
